﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using Contract;
using System.Data.SqlClient;
using System.Data;
using System.ServiceModel;
using System.Net;
using service;
using System.Windows.Forms;
using System.Net.Sockets;
using System.Threading;
namespace Service
{


    public class DataUploader : IDataUploader
    {
        string ServiceAllocator_IP = "127.0.0.1";
        MyFile TempMF = new MyFile();
        string TempName = "";
        string TempPCN = "";
        FileStream TempStream = null;
        List<IPAddress> clientIP = new List<IPAddress>();

        public static List<IPAddress> clientsIP = new List<IPAddress>();

        SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|datadirectory|\FileData.mdf;Integrated Security=SSPI;User Instance=True");

        public class info
        {
            public info(IPAddress ip, string pcn)
            {
                this.IP = ip;
                this.PCN = pcn;
            }
            public IPAddress IP { get; set; }
            public string PCN { get; set; }
        }

        public static List<info> infos = new List<info>();

        public IService_Allocation createProxytoServiceallocator(string ip)
        {
            //////////////////////////////////////
            NetTcpBinding binding = new NetTcpBinding();
            binding.TransferMode = TransferMode.Streamed;
            binding.SendTimeout = TimeSpan.MaxValue;
            binding.MaxReceivedMessageSize = 9223372036854775807;

            EndpointAddress address = new EndpointAddress("net.tcp://" + ip + ":8888/Service_Allocation");

            ChannelFactory<IService_Allocation> channel = new ChannelFactory<IService_Allocation>(binding, address);
            IService_Allocation Proxy = channel.CreateChannel();
            return Proxy;
            ///////////////////////////////////////////////
        }

        public IDataUploader createProxytoDataUploader(string ip)
        {
            //////////////////////////////////////
            NetTcpBinding binding = new NetTcpBinding();
            binding.TransferMode = TransferMode.Streamed;
            binding.SendTimeout = TimeSpan.MaxValue;
            binding.MaxReceivedMessageSize = 9223372036854775807;

            EndpointAddress address = new EndpointAddress("net.tcp://" + ip + ":5000/DataUploader");

            ChannelFactory<IDataUploader> channel = new ChannelFactory<IDataUploader>(binding, address);
            IDataUploader Proxy = channel.CreateChannel();
            return Proxy;
            ///////////////////////////////////////////////
        }
        public delegate void DelegateUpload(MyFile mf);
        public void Upload(MyFile Mf)
        {
            string serverPath = Path.Combine("Storage", Mf.FileOwnerPCN);
            if (!Directory.Exists(serverPath))  // if it doesn't exist, create
                Directory.CreateDirectory(serverPath);
            serverPath = Path.Combine(serverPath, Mf.Filename);


            ParameterizedThreadStart tr = new ParameterizedThreadStart(UploadCallback);
            Thread t = new Thread(UploadCallback);
            t.Start(Mf);

            using (FileStream fs = new FileStream(serverPath, FileMode.OpenOrCreate))
            {
                int bufferSize = 1 * 1024 * 1024; // 1 MB buffer
                byte[] buffer = new byte[bufferSize];
                int bytes;

                while ((bytes = Mf.Value.Read(buffer, 0, bufferSize)) > 0)
                {
                    fs.Write(buffer, 0, bytes);
                    fs.Flush();
                }
            }


            filetable(Mf);
            callbackRefreshFileList();


        }
        public void UploadCallback(object o)
        {
            MyFile mf = (MyFile)o;
            IDataUploader proxy2 = createProxytoDataUploader("192.168.1.107");

            IPAddress[] threeAgents = new IPAddress[3];
            IService_Allocation proxy = createProxytoServiceallocator("127.0.0.1");

            threeAgents = proxy.getThreeAgent();

            for (int i = 0; i < 3; i++)
            {
                if (threeAgents[i] != null)
                {
                    proxy2.Upload_Deadlock(mf);
                }
            }

        }
        public void Upload_Deadlock(MyFile Mf)
        {

            string serverPath = Path.Combine("Storage", Mf.FileOwnerPCN);

            if (!Directory.Exists(serverPath))  // if it doesn't exist, create
                Directory.CreateDirectory(serverPath);
            serverPath = Path.Combine(serverPath, Mf.Filename);
            //  FileStream fs = new FileStream(serverPath, FileMode.Create);

            using (FileStream fs = new FileStream(serverPath, FileMode.Create))
            {
                int bufferSize = 1 * 1024 * 1024; // 1 MB buffer
                byte[] buffer = new byte[bufferSize];
                int bytes;

                while ((bytes = Mf.Value.Read(buffer, 0, bufferSize)) > 0)
                {
                    fs.Write(buffer, 0, bytes);
                    fs.Flush();
                }
            }

            filetable(Mf);


        }

        public Stream Download(string name, string pcn)
        {
            FileStream result = new FileStream("F", FileMode.Create);
            string fPath = Path.Combine("Storage", pcn, name);
            List<IPAddress> agentList = new List<IPAddress>();
            if (!File.Exists(fPath))
            {
                //create proxy to connect to static IP project to get list of IPs of agents
                IService_Allocation proxy1 = createProxytoServiceallocator("localhost");
                agentList = proxy1.AllAgents();
                IDataUploader proxy2;
                foreach (IPAddress ip in agentList)
                {
                    proxy2 = createProxytoDataUploader(ip.ToString());
                    if (proxy2.GetFiletoAgent(name) != null)
                    {
                        result = (FileStream)proxy2.GetFiletoAgent(name);
                        break;
                    }
                }
            }
            else
            {
                result = null;
                result = new FileStream(fPath, FileMode.Open, FileAccess.Read);
            }
            return result;

        }

        public Stream Downloadgf(string FileID)
        {
            FileStream result = new FileStream("F", FileMode.Create);
            SqlCommand sqlComm = new SqlCommand();
                sqlComm = con.CreateCommand();
                sqlComm.CommandText = @"select FileOwnerPCN from [File] where FileID = '" + FileID + "'";
              SqlCommand sqlComm1 = new SqlCommand();
                sqlComm1 = con.CreateCommand();
            sqlComm1.CommandText = @"select FileName from [File] where FileID = '" + FileID + "'";

                con.Open();
               string OwnerPCN= sqlComm.ExecuteScalar().ToString();
               string FileName = sqlComm1.ExecuteScalar().ToString();
                con.Close();
               
            
            string fPath = Path.Combine("Storage",OwnerPCN ,FileName);
            List<IPAddress> agentList = new List<IPAddress>();
            if (!File.Exists(fPath))
            {
                create proxy to connect to static IP project to get list of IPs of agents
                IService_Allocation proxy1 = createProxytoServiceallocator("localhost");
                agentList = proxy1.AllAgents();
                IDataUploader proxy2;
                foreach (IPAddress ip in agentList)
                {
                    proxy2 = createProxytoDataUploader(ip.ToString());
                    if (proxy2.GetFiletoAgent(FileID) != null)
                    {
                        result = (FileStream)proxy2.GetFiletoAgent(FileID);
                        
                        break;
                    }
                }
            }
            else
            {
                result = null;
                result = new FileStream(fPath, FileMode.Open, FileAccess.Read);
            //}
            return result;

        }

        public bool quitGroup(string PCN, string GroupID)
        {
            con.Open();
            string cmdStr1 = "select * from [Group] where Owner = '" + PCN + "'";
            SqlCommand sqlCom1 = new SqlCommand(cmdStr1, con);
            if (sqlCom1.ExecuteScalar() == null)
            {
                string cmdStr = "DELETE FROM [GroupUser] WHERE MemberPCN='" + PCN + "' and GroupID='" + GroupID + "'";
                SqlCommand sqlCom = new SqlCommand(cmdStr, con);
                sqlCom.ExecuteNonQuery();

                con.Close();
                return true;
            }
            else
            {
                string cmdStr = "DELETE FROM [Group] WHERE Owner='" + PCN + "' and GroupID='" + GroupID + "'";
                SqlCommand sqlCom = new SqlCommand(cmdStr, con);
                sqlCom.ExecuteNonQuery();
                callbackRefreshGroupList();
                con.Close();
                return false;
            }
        }

        private void callbackRefreshGroupList()
        {
            NetTcpBinding binding = new NetTcpBinding();
            binding.TransferMode = TransferMode.Buffered;
            binding.SendTimeout = TimeSpan.MaxValue;
            binding.MaxReceivedMessageSize = 100000000;
            binding.PortSharingEnabled = true;
            foreach (info ip in infos)
            {
                string temp = "net.tcp://" + ip.IP.ToString() + ":8001/CallBack";
                EndpointAddress address = new EndpointAddress(temp);

                ChannelFactory<ICallBack> channel = new ChannelFactory<ICallBack>(binding, address);
                ICallBack Proxy = channel.CreateChannel();
                Proxy.CallBackPushGroupList();//Return the complete list of file. Need to be change later on.
            }
        }

        public Stream GetFiletoAgent(string name)
        {
            string fPath = Path.Combine("Storage", name);

            FileStream result;
            if (!File.Exists(fPath))
                result = null;
            else
                result = new FileStream(fPath, FileMode.Open, FileAccess.Read);

            return result;
        }
        //public void DownloadCallback(object o)
        //{
        //    string Name, PCN;
        //    MyFile mf = (MyFile)o;
        //    Name = mf.Filename;
        //    PCN = mf.FileOwnerPCN;
        //    //create proxy to connect to static IP project to get list of IPs of agents
        //    IService_Allocation proxy1 = createProxytoServiceallocator("localhost");
        //    List<IPAddress> agentList = new List<IPAddress>();
        //    agentList = proxy1.AllAgents();
        //    IDataUploader proxy2;
        //    foreach (IPAddress ip in agentList)
        //    {
        //        proxy2 = createProxytoDataUploader(ip.ToString());
        //        if (proxy2.Download_deadlock(Name, PCN) != null)
        //        {
        //            TempStream = (FileStream)proxy2.Download_deadlock(mf.Filename, mf.FileOwnerPCN);
        //            break;
        //        }
        //    }
        //}
        //// new download to avoid deadlock
        //public Stream Download_deadlock(string name, string pcn)
        //{
        //    string fPath = Path.Combine("Storage", pcn, name);

        //    FileStream result;
        //    if (!File.Exists(fPath))
        //        result = null;
        //    else
        //        result = new FileStream(fPath, FileMode.Open, FileAccess.Read);

        //    return result;
        //}

        public void Delete(string name, string pcn)
        {
            TempName = name;
            TempPCN = pcn;

            string serverPath = Path.Combine("Storage", pcn, name);
            if (File.Exists(serverPath))
            {
                File.Delete(serverPath);

                SqlCommand sqlComm = new SqlCommand();
                sqlComm = con.CreateCommand();
                sqlComm.CommandText = @"DELETE FROM [File] WHERE FileName='" + name + "'";

                con.Open();
                sqlComm.ExecuteNonQuery();
                con.Close();

                callbackRefreshFileList();

            }

            List<IPAddress> agentList = new List<IPAddress>();
            //create proxy to with static IP project to get IP list of agents
            IService_Allocation proxy1 = createProxytoServiceallocator(ServiceAllocator_IP);
            agentList = proxy1.AllAgents();
            Thread t = new Thread(new ThreadStart(DeleteCallback));
            t.Start();
        }

        public void DeleteCallback()
        {
            List<IPAddress> agentList = new List<IPAddress>();
            IService_Allocation proxy1 = createProxytoServiceallocator(ServiceAllocator_IP);
            agentList = proxy1.AllAgents();
            foreach (IPAddress ip in agentList)
            {
                IDataUploader proxy2 = createProxytoDataUploader(ip.ToString());
                proxy2.Delete_deadlock(TempName, TempPCN);
            }

        }
        // new delete to avoid deadlock
        public void Delete_deadlock(string name, string pcn)
        {
            string serverPath = Path.Combine("Storage", pcn, name);
            if (File.Exists(serverPath))
            {
                File.Delete(serverPath);

                SqlCommand sqlComm = new SqlCommand();
                sqlComm = con.CreateCommand();
                sqlComm.CommandText = @"DELETE FROM [File] WHERE FileName='" + name + "'";

                con.Open();
                sqlComm.ExecuteNonQuery();
                con.Close();
            }
        }

        public void filetable(MyFile mf)
        {
            SqlCommand SqlCom = new SqlCommand();
            con.Open();

            SqlCom = con.CreateCommand();
            var timeNow = DateTime.Now;

            string Property = "Private";
            string FileID = mf.FileOwnerPCN + mf.Filename;
            SqlCom.Parameters.Add("@paramSize", SqlDbType.VarChar);
            SqlCom.Parameters.Add("@paramPCN", SqlDbType.VarChar);
            SqlCom.Parameters.Add("@paramFileID", SqlDbType.VarChar);
            SqlCom.Parameters.Add("@paramFileName", SqlDbType.VarChar);
            SqlCom.Parameters.Add("@paramFileTime", SqlDbType.VarChar);
            SqlCom.Parameters.Add("@paramProperty", SqlDbType.VarChar);
            SqlCom.Parameters["@paramSize"].Value = mf.Size;
            SqlCom.Parameters["@paramPCN"].Value = mf.FileOwnerPCN;
            SqlCom.Parameters["@paramFileID"].Value = FileID;
            SqlCom.Parameters["@paramFileName"].Value = mf.Filename;
            SqlCom.Parameters["@paramFileTime"].Value = timeNow;
            SqlCom.Parameters["@paramProperty"].Value = Property;
            SqlCom.CommandText = @"INSERT INTO [File] (FileSize, FileID, FileName, FileOwnerPCN, FileUploadTime, Property) VALUES (@paramSize, @paramFileID,@paramFileName,@paramPCN,@paramFileTime,@paramProperty)";
            try
            {
                SqlCom.ExecuteNonQuery();
            }
            catch
            {
                MessageBox.Show("U have a same file name");
            }
            con.Close();
        }

        public int returnNumOfConnections()
        {
            return clientIP.Count;
        }
        //
        public MyFile[] GetfilesList(string PersonalOrGroup)
        {

            string BasePath = "Storage";
            if (!string.IsNullOrEmpty(PersonalOrGroup))
                BasePath = Path.Combine("Storage", PersonalOrGroup);
            DirectoryInfo dirInfo = new DirectoryInfo(BasePath);
            FileInfo[] files = dirInfo.GetFiles("*.*", SearchOption.AllDirectories);

            return (from f in files
                    select new MyFile()
                    {
                        Filename = f.Name,
                        Size = f.Length,
                        Value = null
                    }).ToArray();
        }

        public MyFile[] Getfilesinfo(string pcn)
        {
            con.Open();
            SqlCommand SqlCom1 = new SqlCommand();


            SqlCom1 = con.CreateCommand();


            SqlCom1.CommandText = @"SELECT COUNT(*) FROM [File] where FileOwnerPCN = '" + pcn + "'";
            Int32 topRows = (Int32)SqlCom1.ExecuteScalar();



            string cmdStr = "select * from [File] where FileOwnerPCN ='" + pcn + "'";



            SqlCommand sqlCom = new SqlCommand(cmdStr, con);

            SqlDataReader queryCommandReader = sqlCom.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Load(queryCommandReader);
            MyFile[] f = new MyFile[topRows];
            for (int a = 0; a < topRows; a++)
            {
                f[a] = new MyFile();
            }

            for (int i = 0; i < topRows; i++)
            {
                foreach (DataColumn column in dataTable.Columns)
                {

                    switch (column.ColumnName)
                    {
                        case "FileID":
                            f[i].FileID = dataTable.Rows[i][column.ColumnName].ToString();
                            break;
                        case "FileName":
                            f[i].Filename = dataTable.Rows[i][column.ColumnName].ToString();

                            break;
                        case "FileOwnerPCN":
                            f[i].FileOwnerPCN = dataTable.Rows[i][column.ColumnName].ToString();
                            break;

                        case "FileUploadTime":
                            f[i].FileUploadTime = dataTable.Rows[i][column.ColumnName].ToString();
                            break;
                        case "FileSize":
                            f[i].Size = Convert.ToInt64(dataTable.Rows[i][column.ColumnName]);
                            break;
                    }
                }
            }
            sqlCom.ExecuteScalar();
            con.Close();

            return f;
        }

    

        public MyUser Login(MyUser MU)
        {
            // SqlConnection con = new SqlConnection(@"Data Source=.\SqlEXPRESS;AttachDBFilename=|DataDirectory|\DataBase1.mdf;Integrated Security=True;User Instance=True");
            con.Open();
            string cmdStr = "select * from [User] where PCN ='" + MU.PCN + "' and Password ='" + MU.Password + "' and Property = 'active'";

            SqlCommand sqlCom = new SqlCommand(cmdStr, con);


            if (sqlCom.ExecuteScalar() != null)
            {
                con.Close();
                return MU;
            }


            else
            {
                con.Close();
                return null;
            }

        }


        //change password
        public void cpassword(string newpassword, string PCN)
        {
            con.Open();
            string cmdStr = "update [User] set Password ='" + newpassword + "' where PCN ='" + PCN + "'";

            SqlCommand sqlCom = new SqlCommand(cmdStr, con);
            sqlCom.ExecuteScalar();
            con.Close();

        }

        public int Register(string Email, string Password, string Fname, string Lname, string Gender)
        {

            //bool success = true;
            Random r = new Random();
            int RandomPCN = r.Next(10000000, 99999999);

            // string cmdStr = "INSERT INTO [User] (PCN, Password, Email, Fname, Lname,Gender) values('" + RandomPCN + "','" +Password + "','" +Email + "','" + Fname + "','" + Lname + "','" + Gender + "')";


            SqlCommand SqlCom = new SqlCommand();
            // SqlConnection con = new SqlConnection(@"Data Source=.\SqlEXPRESS;AttachDBFilename=|DataDirectory|\DataBase1.mdf;Integrated Security=True;User Instance=True");

            con.Open();

            SqlCom = con.CreateCommand();

            string Property = "active";
            SqlCom.Parameters.Add("@paramPCN", SqlDbType.VarChar);
            SqlCom.Parameters.Add("@paramPassword", SqlDbType.VarChar);
            SqlCom.Parameters.Add("@paramEmail", SqlDbType.VarChar);
            SqlCom.Parameters.Add("@paramFname", SqlDbType.VarChar);
            SqlCom.Parameters.Add("@paramLname", SqlDbType.VarChar);
            SqlCom.Parameters.Add("@paramGender", SqlDbType.VarChar);
            SqlCom.Parameters.Add("@paramProperty", SqlDbType.VarChar);
            SqlCom.Parameters["@paramPCN"].Value = RandomPCN;
            SqlCom.Parameters["@paramPassword"].Value = Password;
            SqlCom.Parameters["@paramEmail"].Value = Email;
            SqlCom.Parameters["@paramFname"].Value = Fname;
            SqlCom.Parameters["@paramLname"].Value = Lname;
            SqlCom.Parameters["@paramGender"].Value = Gender;
            SqlCom.Parameters["@paramProperty"].Value = Property;
            SqlCom.CommandText = @"INSERT INTO [User] (PCN, Password, Email, Fname, Lname,Gender, Property) VALUES (@paramPCN,@paramPassword,@paramEmail,@paramFname,@paramLname,@paramGender,@paramProperty)";

            SqlCom.ExecuteNonQuery();





            con.Close();
            return RandomPCN;




        }

        public MyGroup CreateGroup(MyGroup mg)
        {
            Random g = new Random();
            int RandomID = g.Next(10000000, 99999999);
            SqlCommand SqlCom = new SqlCommand();
            con.Open();
            var timeNow = DateTime.Now;
            SqlCom = con.CreateCommand();
            MyGroup m = new MyGroup();
            string Property = "active";
            SqlCom.Parameters.Add("@paramGroupID", SqlDbType.VarChar);
            SqlCom.Parameters.Add("@paramGroupName", SqlDbType.VarChar);
            //SqlCom.Parameters.Add("@paramGroupCreateTime", SqlDbType.VarChar);
            SqlCom.Parameters.Add("@paramOwner", SqlDbType.VarChar);
            SqlCom.Parameters.Add("@paramProperty", SqlDbType.VarChar);
            SqlCom.Parameters["@paramGroupID"].Value = RandomID;
            SqlCom.Parameters["@paramGroupName"].Value = mg.GroupName;
            SqlCom.Parameters["@paramOwner"].Value = mg.OwnerPCN;
            // SqlCom.Parameters["@paramGroupCreateTime"].Value = timeNow;
            SqlCom.Parameters["@paramProperty"].Value = Property;
            SqlCom.CommandText = @"INSERT INTO [Group] (GroupID, GroupName,Owner, Property) VALUES (@paramGroupID,@paramGroupName,@paramOwner,@paramProperty)";//,@paramGroupCreateTime, , GroupCreateTime

            SqlCom.ExecuteNonQuery();



            con.Close();

            return m;
        }

        private void callbackRefreshFileList()
        {
            NetTcpBinding binding = new NetTcpBinding();
            binding.TransferMode = TransferMode.Buffered;
            binding.SendTimeout = TimeSpan.MaxValue;
            binding.MaxReceivedMessageSize = 100000000;
            binding.PortSharingEnabled = true;
            foreach (info ip in infos)
            {
                string temp = "net.tcp://" + ip.IP.ToString() + ":8001/CallBack";
                EndpointAddress address = new EndpointAddress(temp);

                ChannelFactory<ICallBack> channel = new ChannelFactory<ICallBack>(binding, address);
                ICallBack Proxy = channel.CreateChannel();
                Proxy.CallBackPushFileList(Getfilesinfo(ip.PCN));//Return the complete list of file. Need to be change later on.
            }
        }

        public bool share(string GroupID, string FileID)
        {
            con.Open();

            string cmdStr1 = "select * from [Group] where GroupID = '" + GroupID + "'";
            SqlCommand sqlCom1 = new SqlCommand(cmdStr1, con);
            if (sqlCom1.ExecuteScalar() != null)
            {
                string cmdStr = "insert into [PublicFile] (GroupID, FileID) values('" + GroupID.ToString() + "','" + FileID.ToString() + "')";
                SqlCommand sqlCom = new SqlCommand(cmdStr, con);
                sqlCom.ExecuteNonQuery();
                con.Close();
                return true;
            }
            else return false;


        }


        public void sendClientIP(IPAddress ip, string pcn)
        {
            infos.Add(new info(ip, pcn));
        }

        public MyGroup[] GetGroupInfo(string pcn)
        {
            con.Open();
            SqlCommand SqlCom1 = new SqlCommand();


            SqlCom1 = con.CreateCommand();


            SqlCom1.CommandText = @"SELECT COUNT(*) FROM [Group] where Owner = '" + pcn + "'";
            Int32 topRows = (Int32)SqlCom1.ExecuteScalar();



            string cmdStr = "select * from [Group] where Owner ='" + pcn + "'";



            SqlCommand sqlCom = new SqlCommand(cmdStr, con);

            SqlDataReader queryCommandReader = sqlCom.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Load(queryCommandReader);
            MyGroup[] g = new MyGroup[topRows];
            for (int a = 0; a < topRows; a++)
            {
                g[a] = new MyGroup();
            }

            for (int i = 0; i < topRows; i++)
            {
                foreach (DataColumn column in dataTable.Columns)
                {

                    switch (column.ColumnName)
                    {
                        case "GroupID":
                            g[i].GroupID = dataTable.Rows[i][column.ColumnName].ToString();
                            break;
                        case "GroupName":
                            g[i].GroupName = dataTable.Rows[i][column.ColumnName].ToString();
                            break;
                        case "Property":
                            g[i].Property = dataTable.Rows[i][column.ColumnName].ToString();
                            break;

                        case "Owner":
                            g[i].OwnerPCN = dataTable.Rows[i][column.ColumnName].ToString();
                            break;

                    }
                }
            }
            sqlCom.ExecuteScalar();
            con.Close();

            return g;
        }

        public MyUser[] GetGroupUser(string GroupID)
        {
            con.Open();
            SqlCommand SqlCom1 = new SqlCommand();
            SqlCom1 = con.CreateCommand();
            SqlCom1.CommandText = @"SELECT COUNT(*) FROM [GroupUser] where GroupID = '" + GroupID + "'";
            Int32 topRows = (Int32)SqlCom1.ExecuteScalar();

            string cmdStr = "select * from [GroupUser] where GroupID ='" + GroupID + "'";
            SqlCommand sqlCom = new SqlCommand(cmdStr, con);
            SqlDataReader queryCommandReader = sqlCom.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Load(queryCommandReader);
            MyUser[] g = new MyUser[topRows];
            for (int a = 0; a < topRows; a++)
            {
                g[a] = new MyUser();
            }

            for (int i = 0; i < topRows; i++)
            {
                foreach (DataColumn column in dataTable.Columns)
                {

                    switch (column.ColumnName)
                    {
                        case "MemberPCN":
                            g[i].PCN = dataTable.Rows[i][column.ColumnName].ToString();
                            break;
                    }
                }
            }
            sqlCom.ExecuteScalar();
            con.Close();

            return g;
        }

        public MyFile[] GetGroupFiles(string GroupID)
        {
            con.Open();
            SqlCommand SqlCom1 = new SqlCommand();
            SqlCom1 = con.CreateCommand();
            SqlCom1.CommandText = @"SELECT COUNT(*) FROM [PublicFile] where GroupID = '" + GroupID + "'";
            Int32 topRows = (Int32)SqlCom1.ExecuteScalar();

            string cmdStr = "select * from [PublicFile] where GroupID ='" + GroupID + "'";
            SqlCommand sqlCom = new SqlCommand(cmdStr, con);
            SqlDataReader queryCommandReader1 = sqlCom.ExecuteReader();
            DataTable dataTable1 = new DataTable();
            dataTable1.Load(queryCommandReader1);
            MyFile[] g = new MyFile[topRows];
            for (int a = 0; a < topRows; a++)
            {
                g[a] = new MyFile();
            }

            for (int i = 0; i < topRows; i++)
            {
                foreach (DataColumn column in dataTable1.Columns)
                {

                    switch (column.ColumnName)
                    {
                        case "FileID":
                            g[i].FileID = dataTable1.Rows[i][column.ColumnName].ToString();
                            break;
                    }
                }

                /* foreach (DataColumn columan in dataTable1.Columns)
                 {
                     string cmdStr2 = "select * from [File] where File ='" + columan + "'";
                     SqlCommand sqlCom2 = new SqlCommand(cmdStr2, con);
                     SqlDataReader queryCommandReader2 = sqlCom.ExecuteReader();
                     DataTable dataTable2 = new DataTable();
                     dataTable1.Load(queryCommandReader1);
                     //MyFile[] g = new MyFile[topRows];
                 }*/
            }
            sqlCom.ExecuteScalar();
            con.Close();
            return g;

        }

        //add GroupMember
        public bool addGroupMember(string memberID, string GroupID)
        {
            bool result=false;
            con.Open();
            string cmdStr1 = "select * from [User] where PCN = '" + memberID + "'";
            SqlCommand sqlCom1 = new SqlCommand(cmdStr1, con);

            //if usxister e
            if (sqlCom1.ExecuteScalar() != null)
            {

                string cmdStr2 = "SELECT * FROM [GroupUser] WHERE MemberPCN = '" + memberID + "' and GroupID = '" + GroupID + "'";
                SqlCommand sqlCom2 = new SqlCommand(cmdStr2, con);

                //if user not exist in this group
                if (sqlCom2.ExecuteScalar() == null)
                {
                    string cmdStr = "insert into [GroupUser] (MemberPCN, GroupID) values('" + memberID + "','" + GroupID + "')";
                    SqlCommand sqlCom = new SqlCommand(cmdStr, con);
                    sqlCom.ExecuteNonQuery();
                    callbackRefreshGroupList();
                    con.Close();
                    result=true;
                }
                else
                {
                    con.Close();
                    result = false;
                }
            }
            return result;

        }

        //deactive User
        public void deactiveUser(string PCN)
        {
            con.Open();
          
           //command string
            string cmdStr = "update [User] set Property='deactive' where PCN ='" + PCN + "'";

            SqlCommand sqlCom = new SqlCommand(cmdStr, con);
            sqlCom.ExecuteScalar();
            con.Close();

        }
    }
}


